package com.zhiyou100.dao.Impl;

import com.alibaba.druid.sql.visitor.functions.Now;
import com.zhiyou100.JDBCUtil.DBUtil;
import com.zhiyou100.dao.RentDao;
import com.zhiyou100.emtity.Rent;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.SQLException;
import java.util.List;

/**
 * @description:
 * @author: 马跃
 * @create: 2021-01-22 22:59
 **/
public class RentDaoImpl implements RentDao {

    private QueryRunner qur = new QueryRunner(DBUtil.getDataSource());

    @Override
    public List<Rent> queryAll() {
        String sql = "SELECT r.*,h.h_address,l.l_name,l.l_phone\n" +
                "FROM rent r\n" +
                "JOIN house h\n" +
                "ON r.h_id=h.h_id\n" +
                "JOIN lessee l\n" +
                "ON r.l_id=l.l_id\n" +
                "GROUP BY 1";
        try {
            List<Rent> query = qur.query(sql, new BeanListHandler<>(Rent.class));
            return query;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    @Override
    public void add(Rent rent) {
        String sql = "insert into rent values(null,?,?,?,now(),?)";
        try {
            qur.update(sql, rent.getH_id(), rent.getL_id(), rent.getR_money(), rent.getR_remark());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return;
    }

    @Override
    public void delete(int id) {
        String sql = "delete from rent where r_id=?";
        try {
            qur.update(sql,id);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return;
    }

    @Override
    public Rent query(int id) {
        String sql = "SELECT r.*,h.h_address,l.l_name,l.l_phone\n" +
                "FROM rent r\n" +
                "JOIN house h\n" +
                "ON r.h_id=h.h_id\n" +
                "JOIN lessee l\n" +
                "ON r.l_id=l.l_id\n" +
                "where r_id=?\n" +
                "GROUP BY 1";
        try {
            Rent query = qur.query(sql, new BeanHandler<>(Rent.class), id);
            return query;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    @Override
    public void update(Rent rent) {
        String sql = "update rent set h_id=?,l_id=?,r_money=?,r_remark=? where r_id=?";
        try {
            qur.update(sql, rent.getH_id(),rent.getL_id(),rent.getR_money(),rent.getR_remark(),rent.getR_id());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return;
    }
}
